package cn.edu.tsinghua.weibo.action;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import cn.edu.tsinghua.weibo.util.ContentFilter;
import cn.edu.tsinghua.weibo.util.DB;
import cn.edu.tsinghua.weibo.util.FormateDate;

public class WeiboAction {
	
	private final static String SQL_FIND_ALL_WEIBO_BY_USERID = "SELECT u.id userid, u.avatar, w.content, w.time, u.nick, w.client, w.id weiboid, w.forwardid, w.picture" +
																" FROM USER u, weibo w" +
																" WHERE u.id=w.userid AND u.id=?" +
																" UNION" +
																" SELECT u.id userid, u.avatar, w.content, w.time, u.nick, w.client, w.id weiboid, w.forwardid, w.picture" +
																" FROM user u, weibo w WHERE u.id=w.userid AND u.id IN (" +
																" SELECT f.followid" +
																" FROM follow f" +
																" WHERE userid=?)" +
																" ORDER BY time DESC";
	
	private final static String SQL_FIND_USER_WEIBO_BY_USERID = "SELECT u.id userid, u.avatar, w.content, w.time, u.nick, w.client, w.id weiboid, w.forwardid, w.picture" +
																" FROM user u, weibo w" +
																" WHERE u.id=w.userid AND u.id=?" +
																" ORDER BY time DESC";
	
	private final static String SQL_FIND_FORWARD_INFO_BY_WEIBOID = "SELECT w.id weiboid, w.content, w.time, w.forwardid, u.id userid, u.nick, u.avatar, w.client, w.picture" +
																" FROM weibo w, user u" +
																" WHERE FIND_IN_SET(w.id, getParentList(?)) AND u.id=w.userid" +
																" ORDER BY w.time DESC";
	
	public static ArrayList<Object[]> findWeiboByUserID(int userid, String scope) {
		
		try {
			Connection connection = DB.getConnection();
			PreparedStatement preparedStatement = null;
			if(scope.equals("all")) {
				preparedStatement = connection.prepareStatement(SQL_FIND_ALL_WEIBO_BY_USERID);
				preparedStatement.setInt(1, userid);
				preparedStatement.setInt(2, userid);
			} else if(scope.equals("user")) {
				preparedStatement = connection.prepareStatement(SQL_FIND_USER_WEIBO_BY_USERID);
				preparedStatement.setInt(1, userid);
			}
			ResultSet resultSet = preparedStatement.executeQuery();
			
			ArrayList<Object[]> arrayList = new ArrayList<Object[]>();
			
			while(resultSet.next()) {
				Object[] objects = new Object[10];
				objects[0] = resultSet.getInt("userid");
				objects[1] = resultSet.getString("avatar");
				objects[2] = ContentFilter.getFiltratedContent(resultSet.getString("content"));
				objects[3] = FormateDate.getFormattedDate(resultSet.getTimestamp("time").getTime());
				objects[4] = resultSet.getString("nick");
				objects[5] = resultSet.getString("client");
				objects[6] = resultSet.getInt("weiboid");
				objects[7] = resultSet.getInt("forwardid");
				if((Integer)objects[7] != 0) {
					objects[8] = getForwardInfo((Integer)objects[7]);
				}
				objects[9] = resultSet.getString("picture");
				arrayList.add(objects);
			}
			DB.close(resultSet, preparedStatement, connection);
			return arrayList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static Map<String, Object> getForwardInfo(int weiboid) {
		Connection connection = DB.getConnection();
		try {
			CallableStatement callableStatement = connection.prepareCall(SQL_FIND_FORWARD_INFO_BY_WEIBOID);
			callableStatement.setInt(1, weiboid);
			ResultSet resultSet = callableStatement.executeQuery();
			Map<String, Object> map = new HashMap<String, Object>();
			String allComments = "";
			Object[] primeWeibo = new Object[8];
			boolean flag = true;
			while(resultSet.next()) {
				if(resultSet.getInt("forwardid") != 0) {//从当前转发的微博开始追溯原始微博，拼接之前转发nick及comment
					allComments += "//@"+resultSet.getString("nick")+"："+resultSet.getString("content");
				}
				if(resultSet.getInt("forwardid") == 0) {//到达了原始微博，取得原始微博信息
					flag = false;//修改标志位
					primeWeibo[0] = resultSet.getInt("weiboid");
					primeWeibo[1] = ContentFilter.getFiltratedContent(resultSet.getString("content"));
					primeWeibo[2] = FormateDate.getFormattedDate(resultSet.getTimestamp("time").getTime());
					primeWeibo[3] = resultSet.getInt("userid");
					primeWeibo[4] = ContentFilter.getFiltratedContent("@"+resultSet.getString("nick"));
					primeWeibo[5] = resultSet.getString("avatar");
					primeWeibo[6] = resultSet.getString("client");
					primeWeibo[7] = resultSet.getString("picture");
				}
			}
			map.put("allComments", ContentFilter.getFiltratedContent(allComments));
			if(flag) {
				primeWeibo = null;
			}
			map.put("primeWeibo", primeWeibo);
			DB.close(resultSet, callableStatement, connection);
			return map;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
